

/// This file generates all tables and figures for the state-year level data


// Font type
graph set window fontface "Times New Roman"
graph set eps fontface "Times New Roman"


**Set "Style"
	grstyle clear , erase
	grstyle init
	grstyle set plain, horizontal nogrid

**Set color scheme for paper
	grstyle set color economist
	
statastates, f(fstate)

rename state_name state 
replace state=proper(state)
keep if _m==3 // drop DC
drop _m

// percent of all expenditures on local ig transfer
gen p_tolocal=state_tot_igr_local/state_tot_exp

// create categories
egen igexp_edu= rowtotal(educneciglocalgovts elemeduciglocalgovts higherediglocgovts )

egen igexp_welfare = rowtotal(welfcategiglocgovts welfcashiglocalgovts welfneciglocalgovts )

egen igexp_healthhosp = rowtotal(healthiglocalgovts totalhospitaliglocgovts )

egen igexp_polcorr = rowtotal(policeprotiglocgovts correctiglocgovts judicialiglocalgovts )

egen igexp_sewwaste = rowtotal(swmgmtiglocalgovts sewerageiglocalgovts)

rename (fireprotiglocalgovts judicialiglocalgovts regularhwyiglocgovts houscomiglocgovts librariesiglocalgovts parksreciglocgovts censtaffiglocalgovts airtransiglocalgovts) (igexp_fire igexp_judicial igexp_highway igexp_house igexp_library igexp_parks igexp_centralstaff igexp_airtransit)


foreach i of varlist igexp* {
	replace `i'=0 if `i'==.
	gen p`i'=`i'/state_tot_igr_local
}

egen test=rowtotal(pigexp*)

global exp pigexp_edu pigexp_welfare pigexp_highway pigexp_healthhosp pigexp_other

gen pigexp_other= 1 - pigexp_edu - pigexp_highway - pigexp_healthhosp - pigexp_welfare 


**Set "Style"
	grstyle clear , erase
	grstyle init
	grstyle set plain, horizontal nogrid

**Set color scheme for paper
	grstyle set color economist
//	grstyle set color   cblind  



cd "$TablesPath/"

///  In-text summary statistics (aggregate to US-year level)

egen transfer=total(state_tot_igr_local), by(year)
egen exp=total(state_tot_exp), by(year)

sum transfer exp if year==2017 // use most recent year in intro



********************************************************************************
*
*	Online Appendix FIGURE B1. Summary Statistics
*
********************************************************************************


graph bar $exp  if fstate<30 , stack over(state_a) vert ///
	xsize(12)  note("")  nofill ///
	ylabel(,labsize(vlarge)) ytitle("Percent of Transfers" "to Local Gov." ,size(huge))  ///
	graphregion(color(white) lcolor(white) lwidth(vvvthick) lstyle(none)) plotregion(lstyle(none) lcolor(white) ) ///
	legend(off) 
	
	graph export bar_state_igexp_1.pdf, replace

	
graph bar $exp  if fstate>=30 , stack over(state_a) vert ///
	xsize(12) note("")  nofill ///
	ylabel(,labsize(vlarge)) ytitle("Percent of Transfers" "to Local Gov." ,size(huge))  ///
	graphregion(color(white) lcolor(white) lwidth(vvvthick) lstyle(none)) plotregion(lstyle(none) lcolor(white) ) ///
	legend(order(1 "Education" 2 "Welfare" 3 "Highway" 4 "Health and Hospitals" 5 "Other") r(1) size(huge) region(lstyle(none) )) 
	
	graph export bar_state_igexp_2.pdf, replace

	
********************************************************************************
*
*	TABLE B1. Summary Statistics at the state level
*
********************************************************************************

foreach i in state_tot_exp state_tot_rev state_tot_igr_local {
	gen `i'_1m=`i'/(10^6) //results reported in dollars
}

label variable state_tot_exp_1m "Expenditures (1M USD)"
label variable state_tot_rev_1m "Revenue (1M USD)"
label variable state_tot_igr_local_1m "Transfers to Local (1M USD)"
label variable p_tolocal "Transfers to Local (\% Exp.)"

label variable pigexp_edu "Education" 
label variable pigexp_welfare "Welfare"
label variable pigexp_highway "Highway" 
label variable pigexp_healthhosp "Health and Hospitals"
label variable pigexp_other  "Other"


mata: mata clear

gen heading1=""
gen heading2=""

label var heading1 "\textit{Total Revenue and Expenditure}"
label var heading2 "\textit{Percent of Transfers, by Purpose}"

local sumvars1  state_tot_exp_1m state_tot_rev_1m state_tot_igr_local_1m p_tolocal
local sumvars2  $exp

foreach k of numlist 1/2 { //number of groups of variables

//compute summary statistics
local count: word count heading`k' `sumvars`k''
mat sumstat = J(`count',2,.)
mat pctile = J(`count',5,.)

local i = 1


foreach var in heading`k' {

	qui: sum `var' , d
	mat sumstat[`i',1] = r(mean)
    mat sumstat[`i',2] = r(sd)
	
	mat pctile[`i',1] = r(min)
    mat pctile[`i',2] = r(p25)
	mat pctile[`i',3] = r(p50)
    mat pctile[`i',4] = r(p75)	
	mat pctile[`i',5] = r(max)
	
	local ++ i 
	
foreach var in `sumvars`k'' {
	
	// indent label in Tex table
	local old_label : variable label `var'
	label variable `var' "\quad `old_label'"

    qui: sum `var' , d
	mat sumstat[`i',1] = r(mean)
    mat sumstat[`i',2] = r(sd)
	
	mat pctile[`i',1] = r(p1)
    mat pctile[`i',2] = r(p25)
	mat pctile[`i',3] = r(p50)
    mat pctile[`i',4] = r(p75)	
	mat pctile[`i',5] = r(p99)
	
	local ++ i 
}
}

//convert matrix to table
matrix rownames sumstat= heading`k' `sumvars`k''
frmttable, statmat(sumstat) store(sumstat`k') sfmt(f,f)   varlabels
frmttable, statmat(pctile)  store(pctile`k')  sfmt(f, f, f, f, f) varlabels
}


count 
local N `r(N)'


//merge and append  tables


outreg ,  ///
	replay(sumstat1) append(sumstat2) store(sumstat12) ///
	tex nocenter note("") fragment plain /// 
	ctitles( "" "Mean" "SD" ) 	
	
outreg , ///
	replay(pctile1) append(pctile2) store(pctile12) ///
	tex nocenter note("") fragment plain /// 
	ctitles("p1" "p25" "p50" "p75" "p99")  
	
outreg using sumstats_statexp.tex, replace ///
	replay(sumstat12) merge(pctile12) ///
	tex nocenter note("") fragment plain   ///
	addrows("Number of Observations" , "`N'") hlines(01{0}10)
